/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: WHY
 * Date: 2023-02-27
 * Time: 19:50
 */
public class TestDemo {
    mysql> show databases;
+---------------------+
        | Database            |
            +---------------------+
            | information_schema  |
            | attendence_checking |
            | caradmin            |
            | java105             |
            | java107             |
            | java108             |
            | mysql               |
            | performance_schema  |
            | sys                 |
            +---------------------+
            9 rows in set (0.01 sec)

    mysql> show tables;
    ERROR 1046 (3D000): No database selected
    mysql> use java108;
    Database changed
    mysql> show tables;
+-------------------+
        | Tables_in_java108 |
            +-------------------+
            | article           |
            | article1          |
            | goods             |
            | library           |
            | student           |
            | teacher           |
            +-------------------+
            6 rows in set (0.00 sec)

    mysql> select* from caradmin;
    ERROR 1146 (42S02): Table 'java108.caradmin' doesn't exist
    mysql> use caradmin;
    Database changed
    mysql> show tables;
+--------------------+
        | Tables_in_caradmin |
            +--------------------+
            | adminer            |
            | car                |
            | norule             |
            +--------------------+
            3 rows in set (0.00 sec)

    mysql> desc norule;
+-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
            +-------+-------------+------+-----+---------+-------+
            | id    | int(11)     | YES  |     | NULL    |       |
            | name  | varchar(20) | YES  |     | NULL    |       |
            +-------+-------------+------+-----+---------+-------+
            2 rows in set (0.01 sec)

    mysql> select* from adminer;
+------+--------------+
        | id   | name         |
            +------+--------------+
            |    1 | 王紫         |
            |    2 | 魏鸿雁       |
            |    3 | 江宇         |
            |    4 | 刘佳艺       |
            |    1 | 奔驰         |
            |    2 | 劳斯莱斯     |
            |    3 | 路虎         |
            |    4 | 法拉利       |
            +------+--------------+
            8 rows in set (0.00 sec)

    mysql> select*from car;
    Empty set (0.00 sec)

    mysql> drop table adminer;
    Query OK, 0 rows affected (0.02 sec)

    mysql> create table adminer;
    ERROR 1113 (42000): A table must have at least 1 column
    mysql> create table adminer(adminer_id int,adminer_name varchar(20));
    Query OK, 0 rows affected (0.04 sec)

    mysql> insert into adminer values(1,'')
    -> ^C
    mysql> insert into adminer values(1,'王紫');
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into adminer values(1,'刘佳艺');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into adminer values(1,'江宇');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into adminer values(1,'魏鸿雁');
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into adminer values(1,'李胤蓉');
    Query OK, 1 row affected (0.00 sec)

    mysql> drop table adminer;
    Query OK, 0 rows affected (0.01 sec)

    mysql> create table adminer(adminer_id  int ,adminer_name varchar(20));
    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into adminer values(1,'王紫'),(2,'刘佳艺'),(3,'江宇'),(4,'魏鸿雁'),(5,'李胤蓉'),(6,'张月莹');
    Query OK, 6 rows affected (0.01 sec)
    Records: 6  Duplicates: 0  Warnings: 0

    mysql> select* from adminer;
+------------+--------------+
        | adminer_id | adminer_name |
            +------------+--------------+
            |          1 | 王紫         |
            |          2 | 刘佳艺       |
            |          3 | 江宇         |
            |          4 | 魏鸿雁       |
            |          5 | 李胤蓉       |
            |          6 | 张月莹       |
            +------------+--------------+
            6 rows in set (0.00 sec)

    mysql> drop table car;
    Query OK, 0 rows affected (0.01 sec)

    mysql> create table carnews(caruser_id int,carname varchar(20),carnumber int);
    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into car values(1,'路虎',20040207),(2,'')
            -> ^C
    mysql> insert into car values(1,'路虎',20040207),(2,'宝马',20051227),(3,'法拉利',20030409),(4,'大奔',20030214),(5,'迈巴赫',20050220),(6,'劳斯莱斯',20021230);
    ERROR 1146 (42S02): Table 'caradmin.car' doesn't exist
    mysql> create table carnews(caruser_id int,carname varchar(20),carnumber int);
    ERROR 1050 (42S01): Table 'carnews' already exists
    mysql> insert into carnews values(1,'路虎',20040207),(2,'宝马',20051227),(3,'法拉利',20030409),(4,'大奔',20030214),(5,'迈巴赫',20050220),(6,'劳斯莱斯',20021230);
    Query OK, 6 rows affected (0.01 sec)
    Records: 6  Duplicates: 0  Warnings: 0

    mysql> select* from carnews;
+------------+--------------+-----------+
        | caruser_id | carname      | carnumber |
            +------------+--------------+-----------+
            |          1 | 路虎         |  20040207 |
            |          2 | 宝马         |  20051227 |
            |          3 | 法拉利       |  20030409 |
            |          4 | 大奔         |  20030214 |
            |          5 | 迈巴赫       |  20050220 |
            |          6 | 劳斯莱斯     |  20021230 |
            +------------+--------------+-----------+
            6 rows in set (0.00 sec)

    mysql> select* from adminer,carnews as norule1;
+------------+--------------+------------+--------------+-----------+
        | adminer_id | adminer_name | caruser_id | carname      | carnumber |
            +------------+--------------+------------+--------------+-----------+
            |          1 | 王紫         |          1 | 路虎         |  20040207 |
            |          2 | 刘佳艺       |          1 | 路虎         |  20040207 |
            |          3 | 江宇         |          1 | 路虎         |  20040207 |
            |          4 | 魏鸿雁       |          1 | 路虎         |  20040207 |
            |          5 | 李胤蓉       |          1 | 路虎         |  20040207 |
            |          6 | 张月莹       |          1 | 路虎         |  20040207 |
            |          1 | 王紫         |          2 | 宝马         |  20051227 |
            |          2 | 刘佳艺       |          2 | 宝马         |  20051227 |
            |          3 | 江宇         |          2 | 宝马         |  20051227 |
            |          4 | 魏鸿雁       |          2 | 宝马         |  20051227 |
            |          5 | 李胤蓉       |          2 | 宝马         |  20051227 |
            |          6 | 张月莹       |          2 | 宝马         |  20051227 |
            |          1 | 王紫         |          3 | 法拉利       |  20030409 |
            |          2 | 刘佳艺       |          3 | 法拉利       |  20030409 |
            |          3 | 江宇         |          3 | 法拉利       |  20030409 |
            |          4 | 魏鸿雁       |          3 | 法拉利       |  20030409 |
            |          5 | 李胤蓉       |          3 | 法拉利       |  20030409 |
            |          6 | 张月莹       |          3 | 法拉利       |  20030409 |
            |          1 | 王紫         |          4 | 大奔         |  20030214 |
            |          2 | 刘佳艺       |          4 | 大奔         |  20030214 |
            |          3 | 江宇         |          4 | 大奔         |  20030214 |
            |          4 | 魏鸿雁       |          4 | 大奔         |  20030214 |
            |          5 | 李胤蓉       |          4 | 大奔         |  20030214 |
            |          6 | 张月莹       |          4 | 大奔         |  20030214 |
            |          1 | 王紫         |          5 | 迈巴赫       |  20050220 |
            |          2 | 刘佳艺       |          5 | 迈巴赫       |  20050220 |
            |          3 | 江宇         |          5 | 迈巴赫       |  20050220 |
            |          4 | 魏鸿雁       |          5 | 迈巴赫       |  20050220 |
            |          5 | 李胤蓉       |          5 | 迈巴赫       |  20050220 |
            |          6 | 张月莹       |          5 | 迈巴赫       |  20050220 |
            |          1 | 王紫         |          6 | 劳斯莱斯     |  20021230 |
            |          2 | 刘佳艺       |          6 | 劳斯莱斯     |  20021230 |
            |          3 | 江宇         |          6 | 劳斯莱斯     |  20021230 |
            |          4 | 魏鸿雁       |          6 | 劳斯莱斯     |  20021230 |
            |          5 | 李胤蓉       |          6 | 劳斯莱斯     |  20021230 |
            |          6 | 张月莹       |          6 | 劳斯莱斯     |  20021230 |
            +------------+--------------+------------+--------------+-----------+
            36 rows in set (0.00 sec)

    mysql> select* from adminer,carnews as norule1 where adminer_id=caruser_id;
+------------+--------------+------------+--------------+-----------+
        | adminer_id | adminer_name | caruser_id | carname      | carnumber |
            +------------+--------------+------------+--------------+-----------+
            |          1 | 王紫         |          1 | 路虎         |  20040207 |
            |          2 | 刘佳艺       |          2 | 宝马         |  20051227 |
            |          3 | 江宇         |          3 | 法拉利       |  20030409 |
            |          4 | 魏鸿雁       |          4 | 大奔         |  20030214 |
            |          5 | 李胤蓉       |          5 | 迈巴赫       |  20050220 |
            |          6 | 张月莹       |          6 | 劳斯莱斯     |  20021230 |
            +------------+--------------+------------+--------------+-----------+
            6 rows in set (0.00 sec)

    mysql> create database resturant_manger;
    Query OK, 1 row affected (0.00 sec)

    mysql> use resturant ;
    ERROR 1049 (42000): Unknown database 'resturant'
    mysql> use resturant_manger;
    Database changed
    mysql> create table foods(id int,name varchar(20));
    Query OK, 0 rows affected (0.03 sec)

    mysql> insert into foods values(1,'清炒西蓝花'),(2,'麻婆豆腐'),(3,'宫保鸡丁'),(4,'鱼香肉丝'),(5,'番茄炒蛋'),(6,'木耳炒肉');
    ERROR 1366 (HY000): Incorrect string value: '\xE6\xB8\x85\xE7\x82\x92...' for column 'name' at row 1
    mysql> drop table resturant;
    ERROR 1051 (42S02): Unknown table 'resturant_manger.resturant'
    mysql> drop table resturant_manger;
    ERROR 1051 (42S02): Unknown table 'resturant_manger.resturant_manger'
    mysql> drop table foods;
    Query OK, 0 rows affected (0.01 sec)

    mysql> create database resturant_manger;
    ERROR 1007 (HY000): Can't create database 'resturant_manger'; database exists
    mysql> drop database resturant_manger;
    Query OK, 0 rows affected (0.01 sec)

    mysql> create database resturant_manger charset utf8;
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into foods values(1,'清炒西蓝花'),(2,'麻婆豆腐'),(3,'宫保鸡丁'),(4,'鱼香肉丝'),(5,'番茄炒蛋'),(6,'木耳炒肉');
    ERROR 1046 (3D000): No database selected
    mysql> use rasturant_manger;
    ERROR 1049 (42000): Unknown database 'rasturant_manger'
    mysql> create table foods(id int,name varchar(20));
    ERROR 1046 (3D000): No database selected
    mysql> use resturant_manger;
    Database changed
    mysql> create table foods(id int,name varchar(20));
    Query OK, 0 rows affected (0.03 sec)

    mysql> insert into foods values(1,'清炒西蓝花'),(2,'麻婆豆腐'),(3,'宫保鸡丁'),(4,'鱼香肉丝'),(5,'番茄炒蛋'),(6,'木耳炒肉');
    Query OK, 6 rows affected (0.01 sec)
    Records: 6  Duplicates: 0  Warnings: 0

    mysql> select* from foods;
+------+-----------------+
        | id   | name            |
            +------+-----------------+
            |    1 | 清炒西蓝花      |
            |    2 | 麻婆豆腐        |
            |    3 | 宫保鸡丁        |
            |    4 | 鱼香肉丝        |
            |    5 | 番茄炒蛋        |
            |    6 | 木耳炒肉        |
            +------+-----------------+
            6 rows in set (0.00 sec)

    mysql> create table storehouse(foods_id int,price int);
    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into storehouse(1,5.12),(2,3.25),(3,10.1),(4,12.1),(5,5.1),(6,13.2);
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,5.12),(2,3.25),(3,10.1),(4,12.1),(5,5.1),(6,13.2)' at line 1
    mysql> insert into storehouse(1,5),(2,3),(3,10),(4,12),(5,5),(6,13);
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,5),(2,3),(3,10),(4,12),(5,5),(6,13)' at line 1
    mysql> insert into storehouse values(1,5.12),(2,3.25),(3,10.1),(4,12.1),(5,5.1),(6,13.2);
    Query OK, 6 rows affected (0.01 sec)
    Records: 6  Duplicates: 0  Warnings: 0

    mysql> select* from stoerhouse;
    ERROR 1146 (42S02): Table 'resturant_manger.stoerhouse' doesn't exist
    mysql> select* from storehouse;
+----------+-------+
        | foods_id | price |
            +----------+-------+
            |        1 |     5 |
            |        2 |     3 |
            |        3 |    10 |
            |        4 |    12 |
            |        5 |     5 |
            |        6 |    13 |
            +----------+-------+
            6 rows in set (0.00 sec)

    mysql> create table  moneyrecord(id int ,count int,price int);
    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into moneyrecord(1,1,5),(2,2,6),(3,1,10)(4,1,12),(5,2,10),(6,1,13);
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1,5),(2,2,6),(3,1,10)(4,1,12),(5,2,10),(6,1,13)' at line 1
    mysql> insert into moneyrecord values(1,1,5),(2,2,6),(3,1,10)(4,1,12),(5,2,10),(6,1,13);
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(4,1,12),(5,2,10),(6,1,13)' at line 1
    mysql> insert into moneyrecord values(1,1,5),(2,2,6),(3,1,10),(4,1,12),(5,2,10),(6,1,13);
    Query OK, 6 rows affected (0.01 sec)
    Records: 6  Duplicates: 0  Warnings: 0

    mysql> select* from moneyrecord;
+------+-------+-------+
        | id   | count | price |
            +------+-------+-------+
            |    1 |     1 |     5 |
            |    2 |     2 |     6 |
            |    3 |     1 |    10 |
            |    4 |     1 |    12 |
            |    5 |     2 |    10 |
            |    6 |     1 |    13 |
            +------+-------+-------+
            6 rows in set (0.00 sec)

    mysql>

    select sex,avg(salary) from emp group by sex;
    select depart,sum(salary) from emp group by depart;
    select depart,sum(salary) from emp group by depart order by sum(salary) desc limit 1,1;
    select name from emp group by name having count(name)>1;
    select depart,avg(salary) from emp where salary>10000 and sex='男' group by depart;

    select B.name,A.grade,B.age from B left join A on A.name=B.name

}
